﻿using Blm.PgsqlDriver.Helpers;
using Blm.Utils.Extensions;
using Language.Resource;
using Magicodes.ExporterAndImporter.Core.Extension;
using Microsoft.EntityFrameworkCore.Internal;
using Microsoft.Extensions.Localization;
using Microsoft.Extensions.Options;
using Newtonsoft.Json;
using Npgsql;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using VisionCloud.Domain.Common;
using VisionCloud.Domain.DtoModel.Applet.RequestDto;
using VisionCloud.Domain.DtoModel.Ms;
using VisionCloud.Domain.Repository.Interface.Ms;
using VisionCloud.Utility;
using VisionCloud.Utility.Option;

namespace VisionCloud.Service.Ms
{
    /// <summary>
    /// 质量分析服务类
    /// </summary>
    public class QualityAnalyzeService
    {
        public IMsProjectRepository MsProjectRepository { get; set; }

        public IMsProjectStandardRepository MsProjectStandardRepository { get; set; }

        public IStringLocalizer<ServiceResource> Localizer { get; set; }

        public StatisService statisService { get; set; }

        private OperatorInfo tokenModel => OperatorProvider.Get();

        private UploadFileOptions uploadFileOptions;

        public QualityAnalyzeService(IOptionsMonitor<UploadFileOptions> option)
        {
            uploadFileOptions = option.CurrentValue;
        }

        /// <summary>
        /// 作者：黄钧航
        /// 获取每个项目的信息，具体每个项目的合格率信息
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public async Task<List<ProjectQualityStatisDto>> GetProjectQualityData(MsCondition condition)
        {
            var data = await MsProjectRepository.GetProjectForChart(condition, OperatorProvider.Get().CompanyGuid);
            List<ProjectQualityStatisDto> projectStatisDto = new List<ProjectQualityStatisDto>();
            foreach (var item in data)
            {
                // 计算项目合格率

                List<NpgsqlParameter> parameters = new List<NpgsqlParameter>();
                NpgsqlParameter npgsqlParameter = new NpgsqlParameter();
                npgsqlParameter.ParameterName = "CompanyGuid";
                npgsqlParameter.Value = OperatorProvider.Get().CompanyGuid;
                parameters.Add(npgsqlParameter);

                npgsqlParameter = new NpgsqlParameter();
                npgsqlParameter.ParameterName = "ProjectGuid";
                npgsqlParameter.Value = item.ProjectId;
                parameters.Add(npgsqlParameter);

                npgsqlParameter = new NpgsqlParameter();
                npgsqlParameter.ParameterName = "BuildingName";
                npgsqlParameter.Value = condition.Build ?? string.Empty;
                parameters.Add(npgsqlParameter);

                npgsqlParameter = new NpgsqlParameter();
                npgsqlParameter.ParameterName = "FloorName";
                npgsqlParameter.Value = condition.Floor ?? string.Empty;
                parameters.Add(npgsqlParameter);

                string sql = VisionCloudGlobalContext.GetSection("sqls:sql:query_project_quality_data");
                using DataSet ds = PgSqlHelper.ExecuteDataSet(sql, parameters.ToArray());
                if (ds != null && ds.Tables.Count > 0)
                {
                    var dt = ds.Tables[0];
                    foreach (DataRow dr in dt.Rows)
                    {
                        int sumPass = 0;
                        int total = 0;
                        foreach (var el in statisService.GetDicItem)
                        {
                            sumPass += (dr[el.Key + "_C"].ToInt32() - dr[el.Key + "_P"].ToInt32());   // 累计合格点数
                            total += dr[el.Key + "_C"].ToInt32(); // 累计测点数
                        }
                        var projectData = new ProjectQualityStatisDto()
                        {
                            ConstructionPermitNum = item.ConstructionPermitNum,  // 施工许可证号
                            ProjectName = item.Name,
                            ProjectId = item.ProjectId,

                            BuildingDetails = JsonConvert.DeserializeObject<List<BuildingDetail>>(item.BuildingDetails),
                            TotalPoints = total,
                            PassPoints = sumPass,
                            Pass = total > 0 ? (sumPass * 1.0 / total * 100).ToString("#0") : "0",
                        };
                        List<ReportInfo> reports = new List<ReportInfo>();
                        if (ds.Tables.Count == 2)
                        {
                            var reportDt = ds.Tables[1];
                            foreach (DataRow report in reportDt.Rows)
                            {
                                // 汇总报告路径
                                string summaryReportPdfUrl = Path.Combine(
                                    report["ResultDataFilePath"].ToString(), report["TaskName"].ToString(), "测量报告", "测量报告.pdf").Replace(@"\", @"/");
                                // 汇总表格列表
                                reports.Add(new ReportInfo()
                                {
                                    Name = report["TaskName"].ToString(),
                                    TaskId = report["TaskId"].ToString(),
                                    BuildingName = report["BuildingName"].ToString(),
                                    FloorName = report["FloorName"].ToString(),
                                    DeviceId = report["DeviceGuid"].ToString(),
                                    UploadedDateTime = DateTime.Parse(report["ReportUploadDateTime"].ToString()),
                                    // 临时服务地址解决方法
                                    Url = StatisService.ToTaskReportUrl(summaryReportPdfUrl).Replace("https://10.8.211.20:8061", "https://blm.bzlrobot.com:8061"),
                                });
                            }
                        }
                        projectData.MeasuringReports = reports;
                        projectStatisDto.Add(projectData);
                        condition.ProjectId = projectData.ProjectId.ToString();

                        string url = await QualityDataToExcelFile(condition);
                        url = url.Replace(@"\", "/");
                        projectData.QualityReportUrl = $"https://blm.bzlrobot.com:8061{url}";  // 质量分析报告地址
                    }
                }
            }
            projectStatisDto.Sort((a, b) =>
            {
                int o = (int)(decimal.Parse(b.Pass) - decimal.Parse(a.Pass));
                return o;
            });

            return projectStatisDto;
        }

        /// <summary>
        /// 作者：黄钧航
        /// 分页获取每个项目的信息，具体每个项目的合格率信息
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public async Task<PagerDto> GetProjectQualityPageData(MsProjectPageCondition condition)
        {
            if (string.IsNullOrWhiteSpace(condition?.ConstructionPermitNum))
            {
                throw new OperationException(Localizer["YB0170"].Value);
            }
            var cond = condition.CopyTo<MsCondition>();
            var result = await this.GetProjectQualityData(cond);
            int total = result.FirstOrDefault() == null ? 0 : result.FirstOrDefault().MeasuringReports.Count;
            if (total > 0)
            {
                result.FirstOrDefault().MeasuringReports = result.FirstOrDefault()?.MeasuringReports.Skip((condition.CurrentPage - 1) * condition.PageSize).Take(condition.PageSize).ToList();
            }
            PagerDto pagerDto = new PagerDto
            {
                PageIndex = condition.CurrentPage,
                PageSize = condition.PageSize,
                Total = total,
                Result = result
            };
            // 前端初始分页页码为1
            return pagerDto;
        }

        /// <summary>
        /// 作者：黄钧航
        /// 获取当前项目的综合、测量项和楼栋合格率
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public async Task<MsSQualityAnalysisDto> GetSQualityAnalysisData(MsCondition condition)
        {
            #region 根据用户选中任务类型和阶段获取项目的综合合格率 公式：总合格点数 / 总测点数

            if (condition.MeasureItemArray == null || condition.MeasureItemArray.Count == 0)
            {
                throw new ArgumentException(Localizer["YB0456"].Value);
            }

            if (condition.TaskPropertyArray == null || condition.TaskPropertyArray.Count == 0)
            {
                throw new ArgumentException(Localizer["YB0457"].Value);
            }

            ProjectOverallPassRatePara projectOverallPassRatePara = new ProjectOverallPassRatePara()
            {
                ProjectId = condition.ProjectId.ToGuid(),
                TaskPropertyArray = condition.TaskPropertyArray,
                TaskTypeArray = condition.TaskTypeArray,
            };

            // 当前项目综合合格率
            List<QualityAnalysisInfo> cqrInfosList = new List<QualityAnalysisInfo>();
            var projectDetailsPassRatePage = GetProjectDetailsPassRatePage(condition, tokenModel.CompanyGuid);
            if (projectDetailsPassRatePage == null)
            {
                return null;
            }
            cqrInfosList.Add(projectDetailsPassRatePage);

            #endregion 根据用户选中任务类型和阶段获取项目的综合合格率 公式：总合格点数 / 总测点数

            #region 综合合格率--根据用户选中任务属性：初测、复测和其他汇总数据 公式：总合格点数 / 总测点数

            List<NpgsqlParameter> parameters = new List<NpgsqlParameter>();
            NpgsqlParameter npgsqlParameter = new NpgsqlParameter
            {
                ParameterName = "CompanyGuid",
                Value = OperatorProvider.Get().CompanyGuid
            };
            parameters.Add(npgsqlParameter);

            npgsqlParameter = new NpgsqlParameter
            {
                ParameterName = "ProjectGuid",
                Value = condition.ProjectId.ToGuid()
            };
            parameters.Add(npgsqlParameter);

            npgsqlParameter = new NpgsqlParameter
            {
                ParameterName = "BuildingName",
                Value = condition.Build ?? string.Empty
            };
            parameters.Add(npgsqlParameter);

            var projects = await MsProjectRepository.GetProjectById(new MsProjectCondition() { ProjectId = condition.ProjectId.ToGuid() });
            if (projects == null || projects.Total == 0)
            {
                throw new OperationException(Localizer["YB0082"].Value);
            }

            // 非初测和复测类型统称为其他
            MsSQualityAnalysisDto msSQualityAnalysisDto = new MsSQualityAnalysisDto();
            string sql1 = string.Format(
                VisionCloudGlobalContext.GetSection("sqls:sql:query_squality_analysis_data01"),
                condition.TransferTaskTypeWhereSql(),
                condition.TransferTaskPropertyWhereSql());

            // 用户选中测量项
            var measureItemDict = condition.TransferMeasureItemDict();

            using (DataSet ds = PgSqlHelper.ExecuteDataSet(sql1, parameters.ToArray()))
            {
                if (ds != null && ds.Tables.Count > 0)
                {
                    var dt = ds.Tables[0];
                    foreach (DataRow dr in dt.Rows)
                    {
                        int sumPass = 0;
                        int total = 0;

                        foreach (var el in measureItemDict)
                        {
                            sumPass += (dr[el.Key + "_C"].ToInt32() - dr[el.Key + "_P"].ToInt32());   // 累计合格点数
                            total += dr[el.Key + "_C"].ToInt32(); // 累计测点数
                        }
                        var cqrInfo = new QualityAnalysisInfo()
                        {
                            Title = $"{dr["ProjectStage"]}{dr["TaskProperty"]}",
                            TotalPoints = total,
                            PassPoints = sumPass,
                            FailPoints = total - sumPass,
                            Pass = total > 0 ? (sumPass * 1.0 / total * 100).ToString("#0") : "0",
                        };
                        cqrInfosList.Add(cqrInfo);
                    }

                    msSQualityAnalysisDto.CQRData.StageData = cqrInfosList;
                }
            }

            #endregion 综合合格率--根据用户选中任务属性：初测、复测和其他汇总数据 公式：总合格点数 / 总测点数

            // 综合合格率--根据项目阶段：结构、砌筑和抹灰阶段汇总数据

            string sql2 = string.Format(
    VisionCloudGlobalContext.GetSection("sqls:sql:query_squality_analysis_data02"),
    condition.TransferTaskTypeWhereSql(), condition.TransferTaskPropertyWhereSql());

            string[] standardDict = { "结构工程", "砌筑工程", "抹灰工程" };
            using (DataSet ds = PgSqlHelper.ExecuteDataSet(sql2, parameters.ToArray()))
            {
                if (ds != null && ds.Tables.Count > 0)
                {
                    List<QualityAnalysisInfo> cqrInfos = new List<QualityAnalysisInfo>();
                    var dt = ds.Tables[0];
                    foreach (DataRow dr in dt.Rows)
                    {
                        //if (dr["ProjectStage"].ToString() != "结构工程" && dr["TaskProperty"].ToString() == "复测")
                        //{
                        //    continue;
                        //}
                        int sumPass = 0;
                        int total = 0;
                        foreach (var el in measureItemDict)
                        {
                            sumPass += (dr[el.Key + "_C"].ToInt32() - dr[el.Key + "_P"].ToInt32());   // 累计合格点数
                            total += dr[el.Key + "_C"].ToInt32(); // 累计测点数
                        }
                        var cqrInfo = new QualityAnalysisInfo()
                        {
                            Title = $"{dr["ProjectStage"]}{dr["NewTaskProperty"]}",
                            TotalPoints = total,
                            PassPoints = sumPass,
                            FailPoints = total - sumPass,
                            Pass = total > 0 ? (sumPass * 1.0 / total * 100).ToString("#0") : "0",
                        };
                        cqrInfos.Add(cqrInfo);
                    }
                    cqrInfos.Sort((a, b) =>
                    {
                        int o = standardDict.IndexOf(a.Title) - standardDict.IndexOf(b.Title);
                        return o;
                    });
                    msSQualityAnalysisDto.CQRData.StandardData = cqrInfos;
                }
            }

            // 测量项合格率

            string[] propertyDict = { "复测", "初测", "其他" };
            string sql3 = string.Format(
VisionCloudGlobalContext.GetSection("sqls:sql:query_squality_analysis_data03"),
condition.TransferTaskTypeWhereSql(), condition.TransferTaskPropertyWhereSql());

            using (DataSet ds = PgSqlHelper.ExecuteDataSet(sql3, parameters.ToArray()))
            {
                if (ds != null && ds.Tables.Count > 0)
                {
                    var dt = ds.Tables[0];
                    List<ProjectStandard> standars = new List<ProjectStandard>();
                    foreach (var st in standardDict)
                    {
                        ProjectStandard standard = new ProjectStandard()
                        {
                            Title = st   // 项目阶段
                        };
                        var drs = dt.Select(string.Format("ProjectStage='{0}'", st));
                        if (drs != null && drs.Length > 0)
                        {
                            List<MeasureItemInfo> mms = new List<MeasureItemInfo>();
                            foreach (var el in measureItemDict)
                            {
                                var mm = new MeasureItemInfo();
                                foreach (var property in propertyDict)  // 构建初测和复测
                                {
                                    var existedRow = dt.Select(string.Format("ProjectStage='{0}' AND NewTaskProperty='{1}'", st, property));
                                    if (existedRow != null && existedRow.Count() > 0)
                                    {
                                        foreach (DataRow dr in existedRow)
                                        {
                                            mm.Title = el.Value;
                                            int sumPass = (dr[el.Key + "_C"].ToInt32() - dr[el.Key + "_P"].ToInt32());
                                            int total = dr[el.Key + "_C"].ToInt32();
                                            var cqrInfo = new QualityAnalysisInfo()  // 对应测量项的测量数据
                                            {
                                                Title = property,//dr["TaskProperty"].ToString(),
                                                TotalPoints = total,
                                                FailPoints = dr[el.Key + "_P"].ToInt32(),
                                                PassPoints = sumPass,  // 合格点数,
                                                Pass = total > 0 ? $"{(sumPass * 1.0 / total * 100).ToString("#0")}" : "0",
                                            };

                                            mm.Data.Add(cqrInfo);
                                        }
                                    }
                                    else
                                    {
                                        // 初测或复测没数据构建0值
                                        var cqrInfo = new QualityAnalysisInfo()  // 对应测量项的测量数据
                                        {
                                            Title = property,
                                            TotalPoints = 0,
                                            FailPoints = 0,
                                            PassPoints = 0,  // 合格点数,
                                            Pass = "0",
                                        };

                                        mm.Data.Add(cqrInfo);
                                    }
                                }
                                standard.MeasureItem.Add(mm);
                            }
                            standars.Add(standard);
                        }
                    }
                    msSQualityAnalysisDto.MeasureItemData = standars; // 各个阶段测量项合格率
                }
            }

            // 楼栋综合合格率
            string sql4 = string.Format(
VisionCloudGlobalContext.GetSection("sqls:sql:query_squality_analysis_data04"),
condition.TransferTaskTypeWhereSql(), condition.TransferTaskPropertyWhereSql());

            using (DataSet ds = PgSqlHelper.ExecuteDataSet(sql4, parameters.ToArray()))
            {
                // 计算公式：楼栋合格率 = 楼栋总的爆点数（结构复测 + 砌筑复测 + 抹灰复测）/ 楼栋总的测点数（结构复测 + 砌筑复测 + 抹灰复测）
                if (ds != null && ds.Tables.Count > 0)
                {
                    var dt = ds.Tables[0];
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        DataView dataView = dt.DefaultView;
                        DataTable buildingNameDt = dataView.ToTable(true, "BuildingName");
                        string buildingName = string.Empty;
                        foreach (DataRow bn in buildingNameDt.Rows)  // 遍历各个楼栋综合合格率
                        {
                            int sumPass = 0;
                            int total = 0;
                            buildingName = bn["BuildingName"].ToString();
                            foreach (var standard in standardDict)
                            {
                                List<string> eleList = new List<string>();
                                foreach (var property in propertyDict)
                                {
                                    var drs = dt.Select($"BuildingName = '{buildingName}' AND ProjectStage = '{standard}' AND NewTaskProperty = '{property}'");
                                    if (drs.Length > 0)
                                    {
                                        foreach (DataRow dr in drs)
                                        {
                                            // 楼栋和楼层是否存在
                                            //if (eleList.IndexOf($"{dr["BuildingName"]}_{dr["FloorName"]}") >= 0)
                                            //{
                                            //    continue;
                                            //}
                                            //eleList.Add($"{dr["BuildingName"]}_{dr["FloorName"]}");
                                            //buildingName = dr["BuildingName"].ToString();
                                            foreach (var el in measureItemDict)
                                            {
                                                sumPass += (dr[el.Key + "_C"].ToInt32() - dr[el.Key + "_P"].ToInt32());   // 累计合格点数
                                                total += dr[el.Key + "_C"].ToInt32(); // 累计测点数
                                            }
                                        }
                                    }
                                }
                            }
                            var buildingInfo = new QualityAnalysisInfo()
                            {
                                Title = buildingName,
                                TotalPoints = total,
                                PassPoints = sumPass,
                                FailPoints = total - sumPass,
                                Pass = total > 0 ? (sumPass * 1.0 / total * 100).ToString("#0") : "0",
                            };
                            msSQualityAnalysisDto.BuildingData.Add(buildingInfo); // 各个阶段测量项合格率
                        }
                    }
                }
            }

            msSQualityAnalysisDto.BuildingData.Sort((a, b) =>
            {
                bool result1, result2;
                result1 = a.Title.IsNumeric();
                result2 = b.Title.IsNumeric();
                if (result1 && result2)
                {
                    if (a.Title.ToInt32() > b.Title.ToInt32()) return 1;
                    if (a.Title.ToInt32() < b.Title.ToInt32()) return -1;
                    return 0;
                }

                if (result1 && !result2)
                {
                    return -1;
                }

                if (!result1 && result2)
                {
                    return 1;
                }
                return string.Compare(a.Title, b.Title, true);
            });

            // 标准测量项

            msSQualityAnalysisDto.MeasureItems = new List<string>();
            foreach (var el in statisService.GetDicItem)
            {
                msSQualityAnalysisDto.MeasureItems.Add(el.Value);
            }

            if (condition.Category == "Details")
            {
                // 各个楼层的测量信息

                var buildingStandardInfos = new List<BuildingStandardInfo>();
                foreach (var st in standardDict)
                {
                    // 按任务阶段取楼层的测量项合格率
                    string sql5 = string.Format(
VisionCloudGlobalContext.GetSection("sqls:sql:query_squality_analysis_data05"),
st, condition.TransferTaskTypeWhereSql(), condition.TransferTaskPropertyWhereSql());

                    List<BuildingMeasureItemInfo> buildings = new List<BuildingMeasureItemInfo>();
                    using (DataSet ds = PgSqlHelper.ExecuteDataSet(sql5, parameters.ToArray()))
                    {
                        if (ds != null && ds.Tables.Count > 0)
                        {
                            var dt = ds.Tables[0];
                            if (dt != null && dt.Rows.Count > 0)
                            {
                                foreach (var el in measureItemDict) // 按测量项分类
                                {
                                    // 计算所有层总测点数
                                    int cntTotalPoints = 0;
                                    var mm = new BuildingMeasureItemInfo() { Title = el.Value }; // 测量项名称
                                    foreach (DataRow row in dt.Rows)
                                    {
                                        // 如果有复测只统计复测数据，没有就统计初测，二选一
                                        if (mm.Data.Exists(x => x.Floor == row["FloorName"].ToString()))
                                        {
                                            continue;
                                        }

                                        var drs = dt.Select(string.Format("BuildingName='{0}' AND FloorName='{1}'",
                                            row["BuildingName"], row["FloorName"]));
                                        if (drs != null && drs.Length > 0) // 初测和复测放
                                        {
                                            foreach (DataRow dr in drs)
                                            {
                                                mm.Title = el.Value;
                                                int sumPass = (dr[el.Key + "_C"].ToInt32() - dr[el.Key + "_P"].ToInt32());
                                                int total = dr[el.Key + "_C"].ToInt32();
                                                var cqrInfo = new QualityAnalysisInfo() // 对应测量项的测量数据
                                                {
                                                    Title = dr["NewTaskProperty"].ToString(),
                                                    Floor = dr["FloorName"].ToString(),
                                                    TotalPoints = total,
                                                    FailPoints = dr[el.Key + "_P"].ToInt32(),
                                                    PassPoints = sumPass, // 合格点数,
                                                    Pass = total > 0 ? (sumPass * 1.0 / total * 100).ToString("#0") : "0",
                                                };
                                                cntTotalPoints += cqrInfo.TotalPoints;
                                                mm.Data.Add(cqrInfo);
                                            }
                                        }
                                    }
                                    // 标记：测量项是否有数据
                                    mm.HasData = cntTotalPoints > 0;
                                    buildings.Add(mm);
                                }
                            }
                        }
                    }
                    buildingStandardInfos.Add(new BuildingStandardInfo()
                    {
                        Title = st,
                        Data = buildings,
                    });
                }

                msSQualityAnalysisDto.Floors = buildingStandardInfos;

                // 各个分户信息

                var hourseStandardInfos = new List<BuildingStandardInfo>();
                foreach (var st in standardDict)
                {
                    // 按任务阶段取分户的测量项合格率
                    string sql6 = string.Format(
VisionCloudGlobalContext.GetSection("sqls:sql:query_squality_analysis_data06"),
st, condition.TransferTaskTypeWhereSql(), condition.TransferTaskPropertyWhereSql());

                    List<BuildingMeasureItemInfo> buildings = new List<BuildingMeasureItemInfo>();
                    using (DataSet ds = PgSqlHelper.ExecuteDataSet(sql6, parameters.ToArray()))
                    {
                        if (ds != null && ds.Tables.Count > 0)
                        {
                            var dt = ds.Tables[0];
                            if (dt != null && dt.Rows.Count > 0)
                            {
                                foreach (var el in measureItemDict) // 按测量项分类
                                {
                                    // 计算所有层总测点数
                                    int cntTotalPoints = 0;
                                    var mm = new BuildingMeasureItemInfo() { Title = el.Value }; // 测量项名称
                                    foreach (DataRow row in dt.Rows)
                                    {
                                        if (mm.Data.Exists(x => x.Floor == row["HouseType"].ToString()))
                                        {
                                            continue;
                                        }

                                        var drs = dt.Select(string.Format("BuildingName='{0}' AND HouseType='{1}'",
                                            row["BuildingName"], row["HouseType"]));
                                        if (drs != null && drs.Length > 0) // 初测和复测放
                                        {
                                            foreach (DataRow dr in drs)
                                            {
                                                mm.Title = el.Value;
                                                int sumPass = (dr[el.Key + "_C"].ToInt32() - dr[el.Key + "_P"].ToInt32());
                                                int total = dr[el.Key + "_C"].ToInt32();
                                                var cqrInfo = new QualityAnalysisInfo() // 对应测量项的测量数据
                                                {
                                                    Title = dr["NewTaskProperty"].ToString(),
                                                    Floor = dr["HouseType"].ToString(),
                                                    TotalPoints = total,
                                                    FailPoints = dr[el.Key + "_P"].ToInt32(),
                                                    PassPoints = sumPass, // 合格点数,
                                                    Pass = total > 0 ? (sumPass * 1.0 / total * 100).ToString("#0") : "0",
                                                };
                                                cntTotalPoints += cqrInfo.TotalPoints;
                                                mm.Data.Add(cqrInfo);
                                            }
                                        }
                                    }
                                    // 标记：测量项是否有数据
                                    mm.HasData = cntTotalPoints > 0;
                                    buildings.Add(mm);
                                }
                            }
                        }
                    }

                    hourseStandardInfos.Add(new BuildingStandardInfo()
                    {
                        Title = st,
                        Data = buildings
                    });
                }

                msSQualityAnalysisDto.Houses = hourseStandardInfos;
                // 各个站点信息

                string sql7 = string.Format(
VisionCloudGlobalContext.GetSection("sqls:sql:query_squality_analysis_data07"),
condition.TransferTaskTypeWhereSql(), condition.TransferTaskPropertyWhereSql());

                using (DataSet ds = PgSqlHelper.ExecuteDataSet(sql7, parameters.ToArray()))
                {
                    var pointStandardInfos = new List<PointStandardInfo>();
                    if (ds != null && ds.Tables.Count > 0)
                    {
                        var dt = ds.Tables[0];
                        List<ProjectStandard> standars = new List<ProjectStandard>();
                        // 各阶段：结构工程、砌筑工程和抹灰工程
                        foreach (var st in standardDict)
                        {
                            if (dt != null && dt.Rows.Count > 0)
                            {
                                List<BuildingMeasureItemInfo> buildings = new List<BuildingMeasureItemInfo>();
                                foreach (var el in measureItemDict)  // 按测量项分类
                                {
                                    // 计算所有层总测点数
                                    int cntTotalPoints = 0;
                                    var mm = new BuildingMeasureItemInfo() { Title = el.Value };  // 测量项名称
                                    foreach (DataRow row in dt.Rows)
                                    {
                                        if (mm.Data.Exists(x => x.Floor == row["PointIndex"].ToString()))
                                        {
                                            continue;
                                        }

                                        var drs = dt.Select(string.Format("PointIndex='{0}' AND ProjectStage='{1}'", row["PointIndex"], st));
                                        if (drs != null && drs.Length > 0) // 初测和复测放
                                        {
                                            foreach (DataRow dr in drs)
                                            {
                                                mm.Title = el.Value;
                                                int sumPass = (dr[el.Key + "_C"].ToInt32() - dr[el.Key + "_P"].ToInt32());
                                                int total = dr[el.Key + "_C"].ToInt32();
                                                var cqrInfo = new QualityAnalysisInfo()  // 对应测量项的测量数据
                                                {
                                                    Title = dr["NewTaskProperty"].ToString(),
                                                    Floor = dr["PointIndex"].ToString(),
                                                    TotalPoints = total,
                                                    FailPoints = dr[el.Key + "_P"].ToInt32(),
                                                    PassPoints = sumPass,  // 合格点数,
                                                    Pass = total > 0 ? (sumPass * 1.0 / total * 100).ToString("#0") : "0",
                                                };
                                                cntTotalPoints += cqrInfo.TotalPoints;
                                                mm.Data.Add(cqrInfo);
                                            }
                                        }
                                    }
                                    // 标记：测量项是否有数据
                                    mm.HasData = cntTotalPoints > 0;
                                    mm.Data = mm.Data.OrderBy(x => int.Parse(x.Floor)).ToList();
                                    buildings.Add(mm);
                                }

                                pointStandardInfos.Add(new PointStandardInfo()
                                {
                                    Title = st,
                                    Data = buildings
                                });
                            }
                        }
                    }
                    msSQualityAnalysisDto.Points = pointStandardInfos;
                }
            }
            return msSQualityAnalysisDto;
        }

        /// <summary>
        /// 作者：黄钧航
        /// 获取当前楼栋的楼层，分户和站点测量数据
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public async Task<MsSQualityAnalysisDto> GetSQualityAnalysisBuildingData(MsCondition condition)
        {
            condition.Category = "Details";
            return await this.GetSQualityAnalysisData(condition);
        }

        /// <summary>
        /// 质量分析分页查询
        /// </summary>
        /// <param name="condition">分页请求参数</param>
        /// <param name="companyGuid">公司Id</param>
        /// <returns></returns>
        private async Task<ApiPageResult> GetProjectPassRatePage(ProjectOverallPassRatePara condition, Guid companyGuid)
        {
            if (condition.TaskPropertyArray == null || condition.TaskPropertyArray.Count == 0)
            {
                throw new ArgumentException(Localizer["YB0457"].Value);
            }

            List<NpgsqlParameter> parameters = new List<NpgsqlParameter>
            {
                new NpgsqlParameter() {ParameterName = "PageSize", Value = condition.PageSize},
                new NpgsqlParameter() {ParameterName = "CurrentPage", Value = condition.CurrentPage - 1},
                new NpgsqlParameter() {ParameterName = "UserId", Value = OperatorProvider.Get().Id},
                // 模糊查询
                new NpgsqlParameter() {ParameterName = "Name", Value = !string.IsNullOrEmpty(condition.Name) ? $"%{condition.Name.ReplaceSQLChar()}%" : string.Empty},
            };

            // 权限控制：公司管理员可用看到所有项目，非公司管理只能看到和自己关联项目信息
            string projectIdWhereSql = statisService.GetRelationProjectsSql("T2", "ProjectId").Result;

            // 根据项目Id获取综合合格率
            if (condition.ProjectId != Guid.Empty)
            {
                projectIdWhereSql = @" AND ( T2.""ProjectId"" = @ProjectId) ";
                parameters.Add(new NpgsqlParameter() { ParameterName = "ProjectId", Value = condition.ProjectId });
            }

            string sql = string.Format(VisionCloudGlobalContext.GetSection("sqls:sql:query_project_pass_rate_page_data"),
                projectIdWhereSql, condition.TransferTaskTypeWhereSql(), condition.TransferTaskPropertyWhereSql());

            var list = await PgSqlHelper.QueryListAsync<ProjectOverallPassRateDto>(sql, parameters.ToArray());
            if (list.Count == 0)
                return new ApiPageResult();

            // 分页数据
            var result = new ApiPageResult()
            {
                Total = list.FirstOrDefault().Total,
                PageData = list
            };
            return result;
        }

        /// <summary>
        /// 质量分析明细综合合格率查询
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="companyGuid"></param>
        /// <returns></returns>

        private QualityAnalysisInfo GetProjectDetailsPassRatePage(MsCondition condition, Guid companyGuid)
        {
            if (condition.MeasureItemArray == null || condition.MeasureItemArray.Count == 0)
            {
                throw new ArgumentException(Localizer["YB0456"].Value);
            }

            if (condition.TaskPropertyArray == null || condition.TaskPropertyArray.Count == 0)
            {
                throw new ArgumentException(Localizer["YB0457"].Value);
            }

            List<NpgsqlParameter> parameters = new List<NpgsqlParameter>
            {
                new NpgsqlParameter() {ParameterName = "CompanyGuid", Value = companyGuid},
                new NpgsqlParameter() {ParameterName = "ProjectGuid", Value = condition.ProjectId.ToGuid()},
                new NpgsqlParameter() {ParameterName = "BuildingName", Value = !string.IsNullOrEmpty(condition.Build) ? condition.Build : string.Empty},
            };

            // 取项目的综合合格率
            string sql1 = string.Format(
                VisionCloudGlobalContext.GetSection("sqls:sql:query_project_details_pass_rate_data"),
                condition.TransferTaskTypeWhereSql(), condition.TransferTaskPropertyWhereSql());

            // 用户选中测量项
            var measureItemDict = condition.TransferMeasureItemDict();
            using DataSet ds = PgSqlHelper.ExecuteDataSet(sql1, parameters.ToArray());
            if (ds != null && ds.Tables.Count > 0)
            {
                var dt = ds.Tables[0];
                int sumPass = 0;
                int total = 0;
                foreach (DataRow dr in dt.Rows)
                {
                    // 遍历用户选中测量项
                    foreach (var el in measureItemDict)
                    {
                        // 累计合格点数
                        sumPass += (dr[el.Key + "_C"].ToInt32() - dr[el.Key + "_P"].ToInt32());
                        // 累计测点数
                        total += dr[el.Key + "_C"].ToInt32();
                    }
                }

                var cqrInfo = new QualityAnalysisInfo()
                {
                    Title = $"综合合格率",
                    TotalPoints = total,
                    PassPoints = sumPass,
                    FailPoints = total - sumPass,
                    Pass = total > 0 ? (sumPass * 1.0 / total * 100).ToString("#0") : "0",
                };
                return cqrInfo;
            }
            return null;
        }

        /// <summary>
        /// 作者：黄钧航
        /// 获取每个项目的信息，具体每个项目的合格率信息
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public async Task<List<ProjectStatisDto>> GetProjectListNew(MsCondition condition)
        {
            Guid companyId = Guid.Empty;
            if (tokenModel?.RoleNameList == "公司管理员")
            {
                companyId = tokenModel.CompanyGuid;
            }
            var data = await MsProjectRepository.GetProjectForChart(condition, companyId);
            List<ProjectStatisDto> projectStatisDto = new List<ProjectStatisDto>();
            string[] standardDict = { "结构工程", "砌筑工程", "抹灰工程" };
            string[] propertyDict = { "复测", "初测" };
            foreach (var item in data)
            {
                List<NpgsqlParameter> parameters = new List<NpgsqlParameter>
                {
                    new NpgsqlParameter("CompanyGuid", OperatorProvider.Get().CompanyGuid),

                    new NpgsqlParameter("ProjectGuid", item.ProjectId)
                };

                string sql = string.Format(VisionCloudGlobalContext.GetSection("sqls:sql:query_project_List"),
                    condition.TransferTaskTypeWhereSql(), condition.TransferTaskPropertyWhereSql());

                using DataSet ds = PgSqlHelper.ExecuteDataSet(sql, parameters.ToArray());
                if (ds != null && ds.Tables.Count > 0)
                {
                    var dt = ds.Tables[0];
                    int sumPass = 0;
                    int total = 0;
                    string projectName = item.Name;
                    Guid projectGuid = item.ProjectId;
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        // 综合合格率 = （结构复测 + 抹灰复测 + 砌筑复测 + 结构初测 + 抹灰初测 + 砌筑初测）合格点数 / （结构复测 + 抹灰复测 + 砌筑复测 + 结构初测 + 抹灰初测 + 砌筑初测）总测点数
                        foreach (var standard in standardDict)
                        {
                            List<string> eleList = new List<string>();
                            foreach (var property in propertyDict)
                            {
                                var drs = dt.Select($"ProjectStage = '{standard}' AND TaskProperty = '{property}'");
                                if (drs.Length > 0)
                                {
                                    foreach (DataRow dr in drs)
                                    {
                                        // 楼栋和楼层是否存在
                                        if (eleList.IndexOf($"{dr["BuildingName"]}_{dr["FloorName"]}") >= 0)
                                        {
                                            continue;
                                        }
                                        eleList.Add($"{dr["BuildingName"]}_{dr["FloorName"]}");
                                        foreach (var el in statisService.GetDicItem)
                                        {
                                            sumPass += (dr[el.Key + "_C"].ToInt32() - dr[el.Key + "_P"].ToInt32());   // 累计合格点数
                                            total += dr[el.Key + "_C"].ToInt32(); // 累计测点数
                                        }
                                    }
                                }
                            }
                        }
                    }

                    var projectData = new ProjectStatisDto()
                    {
                        ProjectName = projectName,
                        ProjectId = projectGuid,
                        TotalPoints = total,
                        PassPoints = sumPass,
                        Pass = total > 0 ? (sumPass * 1.0 / total * 100).ToString("#0") : "0",
                    };
                    projectStatisDto.Add(projectData);
                }
            }
            projectStatisDto.Sort((a, b) =>
            {
                int o = int.Parse(b.Pass) - int.Parse(a.Pass);
                return o;
            });
            return projectStatisDto;
        }

        /// <summary>
        /// 数据分析-质量分析-分页获取报告整体合格率
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public async Task<ApiPageResult> GetProjectOverallPassRatePage(ProjectOverallPassRatePara condition)
        {
            Guid companyId = Guid.Empty;
            if (tokenModel?.RoleNameList == "公司管理员")
            {
                companyId = tokenModel.CompanyGuid;
            }
            var result = await GetProjectPassRatePage(condition, companyId);
            return result;
        }

        /// <summary>
        /// 作者：黄钧航
        /// 质量分析下载数据
        /// </summary>
        /// <param name="condition">用户信息</param>
        /// <returns></returns>
        public async Task<string> QualityDataToExcelFile(MsCondition condition)
        {
            var msSQualityAnalysisDto = await GetSQualityAnalysisBuildingData(condition);
            return await BuildQualityExcel(msSQualityAnalysisDto, condition.ProjectId, condition);
        }

        /// <summary>
        /// 作者：黄钧航
        /// 创建质量分析Excel
        /// </summary>
        /// <returns></returns>
        private async Task<string> BuildQualityExcel(MsSQualityAnalysisDto msSQualityAnalysisDto, string projectId, MsCondition condition)
        {
            if (condition.MeasureItemArray == null || condition.MeasureItemArray.Count == 0)
            {
                throw new ArgumentException(Localizer["YB0456"].Value);
            }

            if (condition.TaskPropertyArray == null || condition.TaskPropertyArray.Count == 0)
            {
                throw new ArgumentException(Localizer["YB0457"].Value);
            }

            // 取每一栋楼的测量项数据，按照初测和复测分类
            string sql = string.Format(VisionCloudGlobalContext.GetSection("sqls:sql:query_build_quality_excel_data"),
                        OperatorProvider.Get().CompanyGuid, projectId, condition.TransferTaskTypeWhereSql(), condition.TransferTaskPropertyWhereSql());

            using DataSet ds = PgSqlHelper.ExecuteDataSet(sql);
            if (ds == null || ds.Tables.Count < 3)
            {
                // 暂无测量数据下载
                throw new OperationException(Localizer["YB0179"].Value);
            }
            if (ds.Tables[0].Rows.Count == 0 || ds.Tables[1].Rows.Count == 0 || ds.Tables[2].Rows.Count == 0)
            {
                // 暂无测量数据下载
                throw new OperationException(Localizer["YB0179"].Value);
            }

            // 获取所有工程阶段
            var listProjectStandards = await MsProjectStandardRepository.GetProjectStandards(projectId.ToGuid());
            if (listProjectStandards == null || listProjectStandards.Count == 0)
            {
                throw new OperationException(Localizer["YB0180"].Value);
            }
            // 下载excel文件
            return ExcelDataExport(ds, listProjectStandards, condition.TransferMeasureItemDict());
        }

        /// <summary>
        /// 作者：黄钧航
        /// Excel数据导出简单示例
        /// </summary>
        /// <param name="data">导出结果</param>
        /// <param name="projectStandards">标准库信息</param>
        /// <param name="measureItemDict"></param>
        /// <returns></returns>
        public string ExcelDataExport(DataSet data, List<StandardListModel> projectStandards, Dictionary<string, string> measureItemDict)
        {
            // 项目名称
            string projectName = data.Tables[0].Rows[0]["ProjectName"].ToString();
            // Excel导出名称
            string excelName = $"{projectName}质量分析统计";
            // 首先创建Excel文件对象
            var workbook = new HSSFWorkbook();
            DataTable tempDT = data.Tables[1];
            var drsSort = tempDT.Rows.Cast<DataRow>().OrderBy(r => r["BuildingName"].ToString());

            DataTable dt0 = data.Tables[0];
            string[] standardDict = { "结构工程", "砌筑工程", "抹灰工程" };
            string[] properties = { "初测", "复测", "其他" };
            foreach (DataRow dr in drsSort)  // 遍历每个楼栋信息
            {
                string building = dr["BuildingName"].ToString();
                string title = $"{dr["ProjectNo"]}-{dr["ProjectName"]}-测量质量统计表{DateTime.Now:yyyy-MM-dd}";
                string subTitle = "子标题";
                int rowCnt = 1;
                // 创建工作表，以栋号作命名(Excel底部名称)
                var sheet = workbook.CreateSheet($"{building}栋");
                sheet.ForceFormulaRecalculation = true;//TODO:是否开始Excel导出后公式仍然有效（非必须）

                #region table 表格内容设置

                #region 标题样式

                // 设置顶部大标题样式
                var cellStyleFont = NpoiExcelExportHelper._.CreateStyle(
                    workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 20, false, 700, "楷体",
                    false, false, false, true, FillPattern.SolidForeground, HSSFColor.LightBlue.Index, HSSFColor.White.Index,
                    FontUnderlineType.None);

                // 设置二级标题样式
                var subCellStyleFont = NpoiExcelExportHelper._.CreateStyle(
                    workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 20, false, 700, "楷体",
                    false, false, false, true, FillPattern.SolidForeground, HSSFColor.Coral.Index, HSSFColor.White.Index,
                    FontUnderlineType.None);

                // 第一行表单(标题：A00XX-0000XX-XXXXXX-测量质量统计表2022-08-02)
                var row = NpoiExcelExportHelper._.CreateRow(sheet, 0, 30);
                // 第一个单元格
                var cell = row.CreateCell(0);
                //合并单元格 例： 第1行到第2行 第3列到第4列围成的矩形区域

                CellRangeAddress region = new CellRangeAddress(0, 0, 0, 41);
                sheet.AddMergedRegion(region);

                // 合并单元格后，只需对第一个位置赋值即可（顶部标题）
                cell.SetCellValue(title);
                // 大标题样式
                cell.CellStyle = cellStyleFont;

                //二级标题列样式设置
                var headTopStyle = NpoiExcelExportHelper._.CreateStyle(
                    workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 15, true, 700, "楷体", true, false, false, true,
                    FillPattern.SolidForeground, HSSFColor.Grey25Percent.Index);

                Dictionary<string, string> headerName = new Dictionary<string, string>();
                headerName.Add("楼层", "楼层");
                foreach (var el in measureItemDict) // 按测量项分类
                {
                    headerName.Add(el.Key, el.Value);
                }
                headerName.Add("综合合格率", "综合合格率"); // 增加列

                float rowHeight = 18f;
                //单元格边框样式
                var cellStyle = NpoiExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 10, true, 400);
                // 遍历所有工程阶段：结构工程、砌筑工程和抹灰工程
                foreach (var item in standardDict)
                {
                    #region 单元格内容信息

                    // 任务属性：初测、复测和其他
                    foreach (var property in properties)
                    {
                        DataRow[] drs = dt0.Select(
                            $"ProjectStage = '{item}' AND BuildingName = '{dr["BuildingName"]}' AND NewTaskProperty = '{property}'");
                        if (drs.Length == 0)
                        {
                            continue;  // 暂无数据
                        }

                        #region 合格率

                        subTitle = $"{building}栋{item}{property}合格率汇总";
                        // 标题区域0~20
                        row = NpoiExcelExportHelper._.CreateRow(sheet, rowCnt, rowHeight);
                        cell = row.CreateCell(0);
                        // XX工程初测合格率汇总0~16列
                        region = new CellRangeAddress(rowCnt, rowCnt, 0, measureItemDict.Count + 1);
                        sheet.AddMergedRegion(region);
                        cell.SetCellValue(subTitle);
                        // 二级标题列样式设置
                        cell.CellStyle = subCellStyleFont;

                        subTitle = $"{building}栋{item}{property}汇总（爆点数/测点数）";
                        cell = row.CreateCell(22);
                        // XX工程初测合格率汇总22~37列
                        region = new CellRangeAddress(rowCnt, rowCnt, 22, 22 + measureItemDict.Count);
                        sheet.AddMergedRegion(region);
                        cell.SetCellValue(subTitle);
                        cell.CellStyle = subCellStyleFont;

                        rowCnt++;
                        row = NpoiExcelExportHelper._.CreateRow(sheet, rowCnt, rowHeight); //第二行

                        int j = 0;
                        foreach (var el in headerName)  // 生成子标题
                        {
                            cell = NpoiExcelExportHelper._.CreateCells(row, headTopStyle, j, el.Value);
                            var existed = projectStandards.FirstOrDefault(x => x.Title.Contains(item));
                            if (existed != null)
                            {
                                cell = NpoiExcelExportHelper._.CreateCells(row, headTopStyle, j, el.Value);
                            }
                            if (el.Value != "综合合格率")
                            {
                                cell = NpoiExcelExportHelper._.CreateCells(row, headTopStyle, j + 22, el.Value);
                            }

                            j++;
                        }

                        #endregion 合格率

                        rowCnt++;

                        for (int i = 0; i < drs.Length; i++)
                        {
                            DataRow dr1 = drs[i];

                            #region 合格率

                            row = NpoiExcelExportHelper._.CreateRow(sheet, i + rowCnt, rowHeight); //sheet.CreateRow(i+2);//在上面表头的基础上创建行
                            cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 0, $"{dr1["FloorName"]}层");
                            int index = 1;
                            int sumPass = 0;
                            int sumMeasure = 0;
                            foreach (var el in measureItemDict) // 按测量项分类
                            {
                                int failure = int.Parse(dr1[$"{el.Key}_P"].ToString());
                                int total = int.Parse(dr1[$"{el.Key}_C"].ToString());
                                int pass = total - failure;
                                sumPass += pass;
                                sumMeasure += total;
                                cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, index, total > 0 ? $"{(pass * 1.0 / total * 100):#0.0}%" : "0%");
                                index++;
                            }

                            cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, index, sumMeasure > 0 ? $"{(sumPass * 1.0 / sumMeasure * 100):#0.0}%" : "0%");
                            index++;

                            #endregion 合格率

                            #region 汇总数

                            cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 22, $"{dr1["FloorName"]}层");
                            index = 23;
                            foreach (var el in measureItemDict) // 按测量项分类
                            {
                                int failure = int.Parse(dr1[$"{el.Key}_P"].ToString());
                                int total = int.Parse(dr1[$"{el.Key}_C"].ToString());
                                int pass = total - failure;
                                cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, index, $"{failure}/{total}");
                                index++;
                            }

                            #endregion 汇总数
                        }

                        rowCnt += drs.Length + 1;
                    }

                    #endregion 单元格内容信息
                }

                #region 平均合格率

                foreach (var item in standardDict)
                {
                    // 任务属性：初测、复测和其他
                    foreach (var property in properties)
                    {
                        DataRow[] dataRows = data.Tables[2].Select($"ProjectStage = '{item}' AND BuildingName='{dr["BuildingName"]}' AND NewTaskProperty = '{property}' ");
                        if (dataRows.Length == 0)
                        {
                            // 暂无数据
                            continue;
                        }

                        subTitle = $"{building}栋各站点{item}{property}平均合格率";
                        row = NpoiExcelExportHelper._.CreateRow(sheet, rowCnt, rowHeight); //sheet.CreateRow(i+2);//在上面表头的基础上创建行
                        cell = row.CreateCell(0);
                        // 0~16列
                        region = new CellRangeAddress(rowCnt, rowCnt, 0, measureItemDict.Count + 1);
                        sheet.AddMergedRegion(region);   // 副标题
                        cell.SetCellValue(subTitle);// 合并单元格后，只需对第一个位置赋值即可（TODO:顶部标题）
                        cell.CellStyle = subCellStyleFont;

                        rowCnt++;
                        row = NpoiExcelExportHelper._.CreateRow(sheet, rowCnt, rowHeight); //第二行
                        cell = NpoiExcelExportHelper._.CreateCells(row, headTopStyle, 0, "站点");

                        int z = 1;
                        foreach (var el in headerName)
                        {
                            if (el.Value == "楼层")
                            {
                                continue;
                            }

                            cell = NpoiExcelExportHelper._.CreateCells(row, headTopStyle, z, el.Value);
                            z++;
                        }
                        rowCnt++;
                        for (int i = 0; i < dataRows.Length; i++)
                        {
                            DataRow dr2 = dataRows[i];
                            row = NpoiExcelExportHelper._.CreateRow(sheet, i + rowCnt, rowHeight); //sheet.CreateRow(i+2);//在上面表头的基础上创建行
                            cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 0, $"{dr2["PointIndex"]}");
                            int index = 1;
                            int sumPass = 0;
                            int sumMeasure = 0;
                            foreach (var el in measureItemDict) // 按测量项分类
                            {
                                int failure = int.Parse(dr2[$"{el.Key}_P"].ToString());
                                int total = int.Parse(dr2[$"{el.Key}_C"].ToString());
                                int pass = total - failure;
                                sumPass += pass;
                                sumMeasure += total;
                                cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, index, total > 0 ? $"{(pass * 1.0 / total * 100):#0.0}%" : "0%");
                                index++;
                            }
                            cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, index, sumMeasure > 0 ? $"{(sumPass * 1.0 / sumMeasure * 100):#0.0}%" : "0%");
                        }

                        rowCnt += (dataRows.Length + 1);
                    }
                }

                #endregion 平均合格率

                #endregion 标题样式

                #endregion table 表格内容设置
            }

            var excelPath = Path.Combine(uploadFileOptions.ParentDir, uploadFileOptions.TemporaryFiles);
            if (!Directory.Exists(excelPath)) Directory.CreateDirectory(excelPath);
            excelPath = Path.Combine(excelPath, excelName + $"_{StatisService.GetRandomNumber()}.xls");

            //使用FileStream文件流来写入数据（传入参数为：文件所在路径，对文件的操作方式，对文件内数据的操作）
            var fileStream = new FileStream(excelPath, FileMode.OpenOrCreate, FileAccess.ReadWrite);

            //向Excel文件对象写入文件流，生成Excel文件
            workbook.Write(fileStream);

            //关闭文件流
            fileStream.Close();

            //释放流所占用的资源
            fileStream.Dispose();
            return excelPath.Replace(uploadFileOptions.ParentDir, string.Empty);
        }

        #region 小程序

        /// <summary>
        /// 根据dto查询质量数据
        /// </summary>
        /// <param name="query"></param>
        public async Task<MeasureDataAnalysisDto> WxGetQualityAnalysisData(WxQualityAnalysisDto query)
        {
            #region 取质量分析数据

            // 取每一栋楼的测量项数据，按照初测和复测分类

            string sql = string.Format(
                VisionCloudGlobalContext.GetSection("sqls:sql:query_wx_quality_analysis_data"),
                OperatorProvider.Get().CompanyGuid, query.ProjectId, query.BuildingName,
                query.FloorName, query.ProjectStage, (int)query.TaskType, WxQualityAnalysisDto.TransferTaskPropertyWhereSql(query.TaskProperty));

            var qualityList = await PgSqlHelper.QueryListAsync<MsSQualityAnalysisResponseDto>(CommandType.Text, sql);
            if (qualityList?.Count == 0)
            {
                // 暂无质量分析数据
                throw new OperationException(AjaxCode.Success, Localizer["YB0063"].Value);
            }

            // 是否有测点
            bool hasMeasurePoint = false;
            foreach (var item in qualityList)
            {
                var result = new int[] {
                        item.R_pingzhengdu_C,
                        item.R_pingzhengdu_P,
                        item.R_chuizhidu_C,
                        item.R_chuizhidu_P,
                        item.R_yinyang_C,
                        item.R_yinyang_P,
                        item.R_fangzhengxing_C,
                        item.R_fangzhengxing_P,
                        item.R_loubanbandishuipingdu_C,
                        item.R_loubanbandishuipingdu_P,
                        item.R_tianhuapingzhengdu_C,
                        item.R_tianhuapingzhengdu_P,
                        item.R_dimianshuipingdu_C,
                        item.R_dimianshuipingdu_P,
                        item.R_dimianpingzhengdu_C,
                        item.R_dimianpingzhengdu_P,
                        item.R_kaijianjinshen_C,
                        item.R_kaijianjinshen_P
                    };
                item.MeasureData = result;
                var existed = result.FirstOrDefault(x => x > 0);
                if (existed != 0 && !hasMeasurePoint)
                {
                    hasMeasurePoint = true;
                }
            }

            if (!hasMeasurePoint)
            {
                // 暂无质量分析数据
                throw new OperationException(AjaxCode.Success, Localizer["YB0474"].Value);
            }

            #endregion 取质量分析数据

            #region 按楼层分组质量数据

            var floorQualityList = new List<FloorQualityDto>();
            foreach (var item in qualityList)
            {
                FloorQualityDto model = new FloorQualityDto() { FloorName = item.FloorName, StNum = new List<string>(), MaxSt = string.Empty };
                if (floorQualityList.Exists(x => x.FloorName == item.FloorName))
                {
                    model = floorQualityList.FirstOrDefault(x => x.FloorName == item.FloorName);
                }
                else
                {
                    floorQualityList.Add(model);
                }

                model.StNum.Add(item.PointIndex);
                model.StCnt = model.StNum.Count;
                model.MaxSt = item.PointIndex;
                model.PointMeasureItemData.Add(item.MeasureData);
            }

            #endregion 按楼层分组质量数据

            #region 按楼栋分组质量数据

            var measureDataAnalysisDto = new MeasureDataAnalysisDto()
            {
                FileName = Guid.NewGuid().ToString(),
                SaveDir = "./MdResult",
                Mids = string.Empty,
                Upd = "1",
                ProjectName = query.ProjectName,
                ProjectStage = query.ProjectStage,
                TaskType = query.TaskType.ToString(),
                TaskProperty = query.TaskProperty.ToString(),
                BuildingName = query.BuildingName,
                Minfo = new List<FloorQualityDto>()
            };
            measureDataAnalysisDto.Minfo = floorQualityList;

            #endregion 按楼栋分组质量数据

            return measureDataAnalysisDto;
        }

        #endregion 小程序
    }
}